﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;

namespace DbLayer
{
    public abstract class DbObject
    {

        SqlConnection _connection;

        protected DbObject()
        {
            _connection = DbSqlConnection.Instance.Connection;
        }

        protected string BuildInsertStatement(string tableName, params string[] values)
        {

            string statement = "INSERT INTO " + tableName + " VALUES (";

            int index = 0;

            foreach (string value in values)
            {

                if (index < values.Length - 1)
                {
                    statement += "'" + value + "',";
                }
                else
                {
                    statement += "'" + values[values.Length - 1] + "');";
                }

                index++;

            }


            return statement;
        }

        protected string BuildDeleteStatement(string tableName, string wClause)
        {
            string statement = "DELETE FROM " + tableName + " WHERE " + wClause + ";";
            return statement;
        }

        protected string BuildUpdateStatement(string tableName, string wClause, params string[] values)
        {

            string statement = "UPDATE " + tableName + " SET ";

            foreach (string value in values)
            {
                statement += value;
            }

            if (wClause.Length > 0)
            {
                statement += " WHERE " + wClause;
            }

            return statement;

        }

        protected void ExecuteStatement(string statement)
        {
            SqlCommand cmd = new SqlCommand(statement, _connection);
            cmd.ExecuteNonQuery();
        }

        protected string BuildSelectStatement(string tableName, string wClause)
        {

            string statement = "SELECT * FROM " + tableName;

            if (wClause.Length > 0)
            {
                statement += " WHERE " + wClause;
            }

            return statement;

        }

        protected SqlDataReader ExecuteQuery(string query)
        {

            SqlCommand cmd = new SqlCommand(query, _connection);
            SqlDataReader dataReader = cmd.ExecuteReader();

            return dataReader;

        }

        protected int GetNextId(string tableName, string columnName)
        {

            int maxId = -1;
            string query = "SELECT MAX(" + columnName + ") FROM " + tableName;
            SqlDataReader reader = ExecuteQuery(query);
            
            if (reader.Read())
            {
                if (reader.IsDBNull(0))
                {
                    maxId = 0;
                }
                else
                {
                    maxId = reader.GetInt32(0) + 1;
                }
            }

            reader.Close();

            return maxId;

        }

    }
}
